﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Oracle.DataAccess.Client;
using DOA = DataService.DataObjectAdapter;
using DSU = DataService.DataServiceUtil;

namespace DataService.CrudClassGenerator
{
    /// <summary>
    /// Provides helper methods for data operations
    /// </summary>
    public abstract class DataObjectHelper
    {
        #region [MEMBERS]  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        private string mDefaultDB = string.Empty;
        /// <summary>
        /// Default Database name to be acted on. 
        /// If this parameter is not given, default database is used. 
        /// Default database name is written in config file as the name of "DefaultDBName"
        /// </summary>
        protected string DefaultDB
        {
            get { return mDefaultDB == string.Empty ? "DefaultDBName".ReadAppSetting<string>() : mDefaultDB; }
            set { mDefaultDB = value; }
        }

        private string mConnectionStringName = string.Empty;
        /// <summary>
        /// ConnectionStringName name to be used. 
        /// If this parameter is not given, default ConnectionStringName is used. 
        /// Default ConnectionStringName is written in config file as the name of "ConnStrSql" for SQL and "ConnStrOracle" for Oracle
        /// </summary>
        protected string ConnectionStringName
        {
            get
            {
                if (mConnectionStringName == string.Empty)
                {
                    if (DataServiceUtil.DatabaseType() == DatabaseTypes.MS_SQL)
                    {
                        return "ConnStrSql";
                    }
                    else if (DataServiceUtil.DatabaseType() == DatabaseTypes.ORACLE)
                    {
                        return "ConnStrOracle";
                    }
                    else
                    {
                        throw new ApplicationException(DataServiceUtil.UnknownDbType);
                    }
                }
                else
                {
                    return mConnectionStringName;
                }

            }

            set
            {
                mConnectionStringName = value;
            }
        }

        private static string mDefaultConnStr = string.Empty;
        /// <summary>
        /// Obtained connection string is passed to this property
        /// </summary>
        protected static string DefaultConnStr { get { return mDefaultConnStr; } set { mDefaultConnStr = value; } }

        /// <summary>
        /// If value of this property is true then existing connection string is used. 
        /// Otherwise, new connection string is taken.
        /// </summary>
        protected static bool UseDefaultCnnStr { get; set; }

        /// <summary>
        /// When value of PageReadOnly property is true and query has insert, update or delete command, this message is thrown
        /// </summary>
        protected const string NoAuthorizationForIUD = "You do not have Authorization for Insert, Update and Delete process on this page!";

        #endregion

        #region [HELPER METHODS] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        /// <summary>
        /// Used to get appropriate Data Adapter for selected database type
        /// </summary>
        /// <param name="pDbCommand">Command object to be acted on</param>
        /// <returns></returns>
        protected DbDataAdapter GetDataAdapter(DbCommand pDbCommand)
        {
            return DOA.GetDataAdapter(pDbCommand);
        }

        /// <summary>
        /// Used to get Identity value after insert operation
        /// </summary>
        /// <param name="pCmd">Command object to be acted on</param>
        /// <param name="pIdentityPrmName">The name of identity parameter</param>
        /// <returns></returns>
        protected DbParameter GetIdentityValue(DbCommand pCmd, string pIdentityPrmName)
        {
            if (DataServiceUtil.DatabaseType() == DatabaseTypes.MS_SQL)
            {
                SqlCommand sqlCmd = (SqlCommand)pCmd;
                return (SqlParameter)sqlCmd.GetIdentityValue(pIdentityPrmName);
            }
            else if (DataServiceUtil.DatabaseType() == DatabaseTypes.ORACLE)
            {
                OracleCommand orclCmd = (OracleCommand)pCmd;
                string prmName = string.Join("", ":", pIdentityPrmName);
                return (OracleParameter)orclCmd.Parameters.Add(prmName, OracleDbType.Int32, ParameterDirection.Output);
            }
            else
            {
                throw new ApplicationException(DataServiceUtil.UnknownDbType);
            }
        }

        /// <summary>
        /// Used to set some necessary properties of command object for selected database type
        /// </summary>
        /// <param name="pCmd">Command object to be acted on</param>
        protected void SetOtherCommandProperties(DbCommand pCmd)
        {
            if (DataServiceUtil.DatabaseType() == DatabaseTypes.MS_SQL)
            {
                //pCmd.PrepareCommandText();
            }
            else if (DataServiceUtil.DatabaseType() == DatabaseTypes.ORACLE)
            {
                pCmd.PrepareCommandText();
                ((OracleCommand)pCmd).BindByName = true;
            }
            else
            {
                throw new ApplicationException(DataServiceUtil.UnknownDbType);
            }
        }

        /// <summary>
        /// Used to check if insert, update and delete processes are allowed or not.
        /// </summary>
        /// <param name="pCommand"></param>
        protected void IsPageReadOnly(DbCommand pCommand)
        {
            if (PageReadOnly)
            {
                if (pCommand.HasIUDCommand()) throw new ApplicationException(NoAuthorizationForIUD);
            }
        }

        /// <summary>
        /// Used to give a table name to DataTable
        /// </summary>
        /// <param name="pDataTable"></param>
        protected void SetTableName(DataTable pDataTable)
        {
            DSU.SetTableName(pDataTable);
        }

        /// <summary>
        /// Used to give a table name to DataSet
        /// </summary>
        /// <param name="pDataSet"></param>
        protected void SetTableName(DataSet pDataSet)
        {
            DSU.SetTableName(pDataSet);
        }

        /// <summary>
        /// Used to add "Initial Catalog" property of SQL connection string
        /// </summary>
        /// <param name="connString">Sql Connection string</param>
        /// <returns></returns>
        protected string AddInitialCatalog(string connString)
        {
            if (!connString.IsContains("Initial Catalog"))
                connString = string.Format("{0} ;Initial Catalog = {1}", connString, mDefaultDB);
            return connString;
        }

        /// <summary>
        /// Used to add "DataSource" property of Oracle connection string
        /// </summary>
        /// <param name="connString">Oracle Connection string</param>
        /// <returns></returns>
        protected string AddDataSource(string connString)
        {
            if (!connString.IsContains("Data Source"))
                connString = string.Format("{0} ;Data Source = {1}", connString, mDefaultDB);
            return connString;
        }

        //-------------------------------------------------------------------------------------

        /// <summary>
        /// ID of person making process 
        /// </summary>
        protected string ProcessorUserID
        {
            get
            {
                string processorId = "unknown";

                // Get processor person unique id

                return processorId;
            }
        }

        /// <summary>
        /// Used to check if the page (or process) is read only
        /// </summary>
        protected bool PageReadOnly
        {
            get
            {
                return false;
            }
        }

        #endregion

        #region [EXCEPTION HANDLING] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        protected void HandleException(Exception ex, DbCommand pCommand)
        {
            //If there is a sql query, it is added to exception object
            if (pCommand != null) ex.Data.Add("SQL", pCommand.CommandText);

            //Caller method name is added to exception obeject
            System.Diagnostics.StackTrace stackTrace = new System.Diagnostics.StackTrace();
            string method = stackTrace.GetFrame(2).GetMethod().ToString();
            if (!string.IsNullOrEmpty(method) && !ex.Data.Contains("Method")) ex.Data.Add("Method", method);

            throw ex;
        }

        #endregion
    }

    /// <summary>
    /// Provides methods for data operations 
    /// </summary>
    public class CCGDataObject : DataObjectHelper
    {
        #region [CONSTRUCTORS] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        /// <summary>
        /// Used to create a CCGDataObject instance
        /// </summary>
        /// <returns></returns>
        public static CCGDataObject Instance()
        {
            return new CCGDataObject();
        }
        /// <summary>
        /// Used to create a CCGDataObject instance
        /// </summary>
        /// <param name="pDefaultDB">Database name to be acted on. 
        /// If this parameter is not given, default database is used. 
        /// Default database name is written in config file as the name of "DefaultDBName</param>
        /// <returns></returns>
        public static CCGDataObject Instance(string pDefaultDB)
        {
            return new CCGDataObject(pDefaultDB);
        }
        /// <summary>
        /// Used to create a CCGDataObject instance
        /// </summary>
        /// <param name="pDefaultDB">Database name to be acted on. 
        /// If this parameter is not given, default database is used. 
        /// Default database name is written in config file as the name of "DefaultDBName</param>
        /// <param name="pConnectionStringName">ConnectionStringName name to be used. 
        /// If this parameter is not given, default ConnectionStringName is used. 
        /// Default ConnectionStringName is written in config file as the name of "ConnStrSql" for SQL and "ConnStrOracle" for Oracle</param>
        /// <returns></returns>
        public static CCGDataObject Instance(string pDefaultDB, string pConnectionStringName)
        {
            return new CCGDataObject(pDefaultDB, pConnectionStringName);
        }

        /// <summary>
        /// Used to create a CCGDataObject instance
        /// </summary>
        /// <returns></returns>
        public CCGDataObject()
        {
            UseDefaultCnnStr = true;
            ConnectionStringName = ConnectionStringName;
        }
        /// <summary>
        /// Used to create a CCGDataObject instance
        /// </summary>
        /// <param name="pDefaultDB">Database name to be acted on. 
        /// If this parameter is not given, default database is used. 
        /// Default database name is written in config file as the name of "DefaultDBName</param>
        /// <returns></returns>
        public CCGDataObject(string pDefaultDB)
        {
            UseDefaultCnnStr = true;
            DefaultDB = pDefaultDB;
            ConnectionStringName = ConnectionStringName;
        }
        /// <summary>
        /// Used to create a CCGDataObject instance
        /// </summary>
        /// <param name="pDefaultDB">Database name to be acted on. 
        /// If this parameter is not given, default database is used. 
        /// Default database name is written in config file as the name of "DefaultDBName</param>
        /// <param name="pConnectionStringName">ConnectionStringName name to be used. 
        /// If this parameter is not given, default ConnectionStringName is used. 
        /// Default ConnectionStringName is written in config file as the name of "ConnStrSql" for SQL and "ConnStrOracle" for Oracle</param>
        /// <returns></returns>
        public CCGDataObject(string pDefaultDB, string pConnectionStringName)
        {
            if (ConnectionStringName != pConnectionStringName)
                UseDefaultCnnStr = false;
            else
                UseDefaultCnnStr = true;

            DefaultDB = pDefaultDB;
            ConnectionStringName = pConnectionStringName;
        }

        #endregion

        #region [EXECUTE] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        /// <summary>
        /// Used to execute a non query sql statement
        /// </summary>
        /// <param name="pCommand">Command object to be acted on</param>
        /// <returns></returns>
        public int ExecuteNonQueryStatement(DbCommand pCommand)
        {
            return ExecuteNonQueryStatement(pCommand, false, false);
        }
        /// <summary>
        /// Used to execute a non query sql statement
        /// </summary>
        /// <param name="pCommand">Command object to be acted on</param>
        /// <param name="pIsTransactional">If true, sql transaction is added</param>
        /// <returns></returns>
        public int ExecuteNonQueryStatement(DbCommand pCommand, bool pIsTransactional)
        {
            return ExecuteNonQueryStatement(pCommand, pIsTransactional, false);
        }
        /// <summary>
        /// Used to execute a non query sql statement
        /// </summary>
        /// <param name="pCommand">Command object to be acted on</param>
        /// <param name="pIsTransactional">If true, sql transaction is added</param>
        /// <param name="pWriteLog">To log results of insert, update or delete process, set as true</param>
        /// <returns></returns>
        public int ExecuteNonQueryStatement(DbCommand pCommand, bool pIsTransactional, bool pWriteLog)
        {
            DbConnection cn = null;

            try
            {
                if (!Util.IsTableExceptionLog(pCommand.CommandText) && PageReadOnly)
                    throw new ApplicationException(NoAuthorizationForIUD);

                if (pWriteLog) WriteAdHocQueryLog(pCommand, pIsTransactional);
                if (pIsTransactional && !pWriteLog) pCommand.AddTransaction();

                SetOtherCommandProperties(pCommand);
                cn = GetDbConnection();
                pCommand.Connection = cn;

                return pCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                HandleException(ex, pCommand);
                return 0;
            }
            finally
            {
                if (cn != null) cn.Close();
            }
        }

        /// <summary>
        /// Used to execute a Scalar sql statement. e.g: Count, Sum, ...
        /// </summary>
        /// <param name="pCommand">Command object to be acted on</param>
        /// <returns></returns>
        public object ExecuteScalarStatement(DbCommand pCommand)
        {
            return ExecuteScalarStatement(pCommand, false);
        }
        /// <summary>
        /// Used to execute a Scalar sql statement. e.g: Count, Sum, ...
        /// </summary>
        /// <param name="pCommand">Command object to be acted on</param>
        /// <param name="pIsTransactional">If true, sql transaction is added</param>
        /// <returns></returns>
        public object ExecuteScalarStatement(DbCommand pCommand, bool pIsTransactional)
        {
            DbConnection cn = null;

            try
            {
                if (pIsTransactional) pCommand.AddTransaction();

                SetOtherCommandProperties(pCommand);
                cn = GetDbConnection();
                pCommand.Connection = cn;

                return pCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                HandleException(ex, pCommand);
                return null;
            }
            finally
            {
                if (cn != null) cn.Close();
            }
        }

        #endregion

        #region [GET RECORDS] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        /// <summary>
        /// Used to get selected records from Database
        /// </summary>
        /// <param name="pDataTable">DataTable object to be filled</param>
        /// <param name="pCommand">Command object to be acted on</param>
        public void GetRecords(DataTable pDataTable, DbCommand pCommand)
        {
            IsPageReadOnly(pCommand);

            DbConnection cn = null;

            try
            {
                SetOtherCommandProperties(pCommand);
                cn = GetDbConnection();
                pCommand.Connection = cn;

                GetDataAdapter(pCommand).Fill(pDataTable); ;

                SetTableName(pDataTable);
            }
            catch (Exception ex)
            {
                HandleException(ex, pCommand);
            }
            finally
            {
                if (cn != null) cn.Close();
            }
        }
        /// <summary>
        /// Used to get selected records from Database
        /// </summary>
        /// <param name="pDataTable">DataSet object to be filled</param>
        /// <param name="pCommand">Command object to be acted on</param>
        public void GetRecords(DataSet pDataSet, DbCommand pCommand)
        {
            IsPageReadOnly(pCommand);

            DbConnection cn = null;

            try
            {
                SetOtherCommandProperties(pCommand);
                cn = GetDbConnection();
                pCommand.Connection = cn;

                GetDataAdapter(pCommand).Fill(pDataSet);

                SetTableName(pDataSet);
            }
            catch (Exception ex)
            {
                HandleException(ex, pCommand);
            }
            finally
            {
                if (cn != null) cn.Close();
            }
        }

        #endregion

        #region [HELPER METHODS] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        private DbConnection GetDbConnection()
        {
            string connString = string.Empty;

            if (!UseDefaultCnnStr || DefaultConnStr == string.Empty)
            {
                connString = DataServiceUtil.GetConnStr(ConnectionStringName);
                DefaultConnStr = connString;
            }
            else
            {
                connString = DefaultConnStr;
            }

            if (DataServiceUtil.DatabaseType() == DatabaseTypes.MS_SQL)
            {
                connString = AddInitialCatalog(connString);

                SqlConnection cn = new SqlConnection(connString);
                cn.Open();
                return cn;
            }
            else if (DataServiceUtil.DatabaseType() == DatabaseTypes.ORACLE)
            {
                connString = AddDataSource(connString);

                OracleConnection cn = new OracleConnection(connString);
                cn.Open();
                return cn;
            }
            else
            {
                throw new ApplicationException(DataServiceUtil.UnknownDbType);
            }
        }

        /// <summary>
        /// Used to log results of insert, update and delete AdHoc Queries
        /// </summary>
        /// <param name="pCommand">DbCommand object to be acted on</param>
        /// <param name="pIsTransactional">If true, process should be in transaction</param>
        /// <param name="pFullAdHocQueryLogTableName">
        /// If this parameter is not given, default AdHock query log table (CRUD_LOG_ADHOC) is used. 
        protected void WriteAdHocQueryLog(DbCommand pCommand, bool pIsTransactional, string pFullAdHocQueryLogTableName = "")
        {
            CCGLog log = new CCGLog();
            if (!string.IsNullOrEmpty(pFullAdHocQueryLogTableName)) log.FullAdHocQueryLogTableName = pFullAdHocQueryLogTableName;
            DbCommand cmd = log.GetAdHocQueryLogCommand(pCommand.Parameters);
            pCommand.MergeCommands(cmd);
            if (pIsTransactional) pCommand.AddTransaction();
        }

        #endregion
    }

    /// <summary>
    /// Provides methods for data operations by using ADO transaction.
    /// </summary>
    public class CCGDataObjectAdoTran : DataObjectHelper
    {
        #region [CONSTRUCTORS] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        /// <summary>
        /// Used to create a CCGDataObjectAdoTran instance
        /// </summary>
        /// <returns></returns>
        public static CCGDataObjectAdoTran Instance()
        {
            return new CCGDataObjectAdoTran();
        }
        /// <summary>
        /// Used to create a CCGDataObjectAdoTran instance
        /// </summary>
        /// <param name="pDefaultDB">Database name to be acted on. 
        /// If this parameter is not given, default database is used. 
        /// Default database name is written in config file as the name of "DefaultDBName</param>
        /// <returns></returns>
        public static CCGDataObjectAdoTran Instance(string pDefaultDB)
        {
            return new CCGDataObjectAdoTran(pDefaultDB);
        }
        /// <summary>
        /// Used to create a CCGDataObjectAdoTran instance
        /// </summary>
        /// <param name="pDefaultDB">Database name to be acted on. 
        /// If this parameter is not given, default database is used. 
        /// Default database name is written in config file as the name of "DefaultDBName</param>
        /// <param name="pConnectionStringName">ConnectionStringName name to be used. 
        /// If this parameter is not given, default ConnectionStringName is used. 
        /// Default ConnectionStringName is written in config file as the name of "ConnStrSql" for SQL and "ConnStrOracle" for Oracle</param>
        /// <returns></returns>
        public static CCGDataObjectAdoTran Instance(string pDefaultDB, string pConnectionStringName)
        {
            return new CCGDataObjectAdoTran(pDefaultDB, pConnectionStringName);
        }

        /// <summary>
        /// Used to create a CCGDataObjectAdoTran instance
        /// </summary>
        /// <returns></returns>
        public CCGDataObjectAdoTran()
        {
            UseDefaultCnnStr = true;
            ConnectionStringName = ConnectionStringName;
        }
        /// <summary>
        /// Used to create a CCGDataObjectAdoTran instance
        /// </summary>
        /// <param name="pDefaultDB">Database name to be acted on. 
        /// If this parameter is not given, default database is used. 
        /// Default database name is written in config file as the name of "DefaultDBName</param>
        /// <returns></returns>
        public CCGDataObjectAdoTran(string pDefaultDB)
        {
            UseDefaultCnnStr = true;
            DefaultDB = pDefaultDB;
            ConnectionStringName = ConnectionStringName;
        }
        /// <summary>
        /// Used to create a CCGDataObjectAdoTran instance
        /// </summary>
        /// <param name="pDefaultDB">Database name to be acted on. 
        /// If this parameter is not given, default database is used. 
        /// Default database name is written in config file as the name of "DefaultDBName</param>
        /// <param name="pConnectionStringName">ConnectionStringName name to be used. 
        /// If this parameter is not given, default ConnectionStringName is used. 
        /// Default ConnectionStringName is written in config file as the name of "ConnStrSql" for SQL and "ConnStrOracle" for Oracle</param>
        /// <returns></returns>
        public CCGDataObjectAdoTran(string pDefaultDB, string pConnectionStringName)
        {
            if (ConnectionStringName != pConnectionStringName)
                UseDefaultCnnStr = false;
            else
                UseDefaultCnnStr = true;

            DefaultDB = pDefaultDB;
            ConnectionStringName = pConnectionStringName;
        }

        #endregion

        #region [MEMBERS] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        private SqlConnection oCn;
        private SqlTransaction oTran;

        #endregion

        #region [TRANSECTION METHODS] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        /// <summary>
        /// Used to begin a transaction. Default isolation level is "ReadCommitted"
        /// </summary>
        public void BeginTransaction()
        {
            BeginTransaction(IsolationLevel.ReadCommitted);
        }
        /// <summary>
        /// Used to begin a transaction
        /// </summary>
        /// <param name="pIsolationLevel">Isolation level can be changed. Default level is "ReadCommitted"</param>
        public void BeginTransaction(IsolationLevel pIsolationLevel)
        {
            if (oCn == null)
            {
                string connString = string.Empty;

                if (!UseDefaultCnnStr || DefaultConnStr == string.Empty)
                {
                    connString = DataServiceUtil.GetConnStr(ConnectionStringName);
                    DefaultConnStr = connString;
                }
                else
                {
                    connString = DefaultConnStr;
                }

                connString = AddInitialCatalog(connString);
                oCn = new SqlConnection(connString);
                oCn.Open();

                oTran = oCn.BeginTransaction(pIsolationLevel);
            }
        }

        /// <summary>
        /// Used to commit existing transaction
        /// </summary>
        public void CommitTransaction()
        {
            try
            {
                if (oTran != null && oTran.Connection != null) oTran.Commit();
            }
            catch (Exception exc)
            {
                throw exc;
            }
            finally
            {
                if (oCn != null) oCn.Close();
            }
        }
        /// <summary>
        /// Used to rollback existing transaction
        /// </summary>
        public void RollbackTransaction()
        {
            try
            {
                if (oTran != null && oTran.Connection != null) oTran.Rollback();
            }
            catch (Exception exc)
            {
                throw exc;
            }
            finally
            {
                if (oCn != null) oCn.Close();
            }
        }

        #endregion

        #region [EXECUTE] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        /// <summary>
        /// Used to execute a non query sql statement
        /// </summary>
        /// <param name="pCommand">Command object to be acted on</param>
        /// <returns></returns>
        public int ExecuteNonQueryStatement(DbCommand pCommand)
        {
            return ExecuteNonQueryStatement(pCommand, false);
        }
        /// <summary>
        /// Used to execute a non query sql statement
        /// </summary>
        /// <param name="pCommand">Command object to be acted on</param>
        /// <param name="pWriteLog">To log results of insert, update or delete process, set as true</param>
        /// <returns></returns>
        public int ExecuteNonQueryStatement(DbCommand pCommand, bool pWriteLog)
        {
            IsPageReadOnly(pCommand);

            try
            {
                if (pWriteLog) WriteAdHocQueryLog(pCommand);

                SetOtherCommandProperties(pCommand);
                pCommand.Connection = oCn;
                pCommand.Transaction = oTran;

                return pCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                HandleException(ex, pCommand);
                return 0;
            }
        }

        /// <summary>
        /// Used to execute a Scalar sql statement. e.g: Count, Sum, ...
        /// </summary>
        /// <param name="pCommand">Command object to be acted on</param>
        /// <returns></returns>
        public object ExecuteScalarStatement(DbCommand pCommand)
        {
            IsPageReadOnly(pCommand);

            try
            {
                SetOtherCommandProperties(pCommand);
                pCommand.Connection = oCn;
                pCommand.Transaction = oTran;

                return pCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                HandleException(ex, pCommand);
                return null;
            }
        }

        #endregion

        #region [GET RECORDS] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        /// <summary>
        /// Used to get selected records from Database
        /// </summary>
        /// <param name="pDataTable">DataTable object to be filled</param>
        /// <param name="pCommand">Command object to be acted on</param>
        public void GetRecords(DataTable pDataTable, DbCommand pCommand)
        {
            IsPageReadOnly(pCommand);

            try
            {
                SetOtherCommandProperties(pCommand);
                pCommand.Connection = oCn;
                pCommand.Transaction = oTran;
                GetDataAdapter(pCommand).Fill(pDataTable);

                SetTableName(pDataTable);
            }
            catch (Exception ex)
            {
                HandleException(ex, pCommand);
            }
        }
        /// <summary>
        /// Used to get selected records from Database
        /// </summary>
        /// <param name="pDataTable">DataSet object to be filled</param>
        /// <param name="pCommand">Command object to be acted on</param>
        public void GetRecords(DataSet pDataSet, DbCommand pCommand)
        {
            IsPageReadOnly(pCommand);

            try
            {
                SetOtherCommandProperties(pCommand);
                pCommand.Connection = oCn;
                pCommand.Transaction = oTran;
                GetDataAdapter(pCommand).Fill(pDataSet);

                SetTableName(pDataSet);
            }
            catch (Exception ex)
            {
                HandleException(ex, pCommand);
            }
        }

        #endregion

        #region [HELPER METHODS] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        private void WriteAdHocQueryLog(DbCommand pCommand)
        {
            DbCommand cmd = CCGLog.Instance().GetAdHocQueryLogCommand(pCommand.Parameters);
            pCommand.MergeCommands(cmd);
        }

        #endregion
    }
}
